package com.example.dao;

import com.example.gui.TransactionManagementPanel;
import com.example.model.CardInfo;
import com.example.model.Deposit;
import com.example.model.TradeInfo;
import com.example.model.UserInfo;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.LinkedList;
import java.util.List;
import java.util.Random;

import static com.example.util.DBUtil.getConnection;

public class AdminDAO {

    // 获取客户信息
    public List<UserInfo> getUserInfo() {
        List<UserInfo> userInfoList = new LinkedList<>();
        String sql = "select * from userinfo";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    UserInfo userInfo = new UserInfo();

                    userInfo.setAddress(rs.getString("address"));
                    userInfo.setCustomerId(rs.getInt("customerId"));
                    userInfo.setCustomerName(rs.getString("customerName"));
                    userInfo.setTelephone(rs.getString("telephone"));
                    userInfo.setPid(rs.getString("pid"));
                    userInfoList.add(userInfo);
                }
            }
        }catch(SQLException e) {
            e.printStackTrace();
            return null;
        }
        return userInfoList;
    }

    // 增加新用户
    public boolean addNewCustomer(UserInfo userInfo){

        String sql = "INSERT INTO userInfo (customerName, PID, telephone, address) VALUES (?, ?, ?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, userInfo.getCustomerName());
            ps.setString(2, userInfo.getPid());
            ps.setString(3, userInfo.getTelephone());
            ps.setString(4, userInfo.getAddress());

            try {
                ps.executeUpdate();
            } catch (SQLException e) {
                return false;
            }

        }catch(SQLException e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }


    // 修改用户信息
    public boolean updateCustomerInfo(UserInfo userInfo){
        String sql = "update userinfo set customerName = ?, PID = ?, telephone = ?, address = ? where customerId = ?";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, userInfo.getCustomerName());
            ps.setString(2, userInfo.getPid());
            ps.setString(3, userInfo.getTelephone());
            ps.setString(4, userInfo.getAddress());
            ps.setInt(5, userInfo.getCustomerId());
            ps.executeUpdate();

            try {
                ps.executeUpdate();
            } catch (SQLException e) {
                return false;
            }

        }catch(SQLException e) {
            e.printStackTrace();
            return false;
        }

        return true;

    }

    public boolean deleteUserInfo(int customerID){
        String sql = "delete from userinfo where customerId = ?";

        try(Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);) {

            ps.setInt(1, customerID);
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;

    }

    // 获得所有客户ID
    public List<String> getAllCustomerID(){
        List<String> customerIDList = new LinkedList<>();
        String sql = "select customerId from userinfo";

        try(Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);){
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    customerIDList.add(rs.getString("customerId"));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return customerIDList;
    }


    // 获取银行卡列表
    public  List<CardInfo> getCardInfo(){
        List<CardInfo> cardInfoList = new LinkedList<>();
        String sql = "select * from cardinfo";

        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    CardInfo cardInfo = new CardInfo();

                    cardInfo.setCardId(rs.getString("cardId"));
                    cardInfo.setCurId(rs.getString("curId"));
                    cardInfo.setSavingId(rs.getInt("savingId"));
                    Timestamp timestamp = rs.getTimestamp("openDate");
                    cardInfo.setOpenDate(timestamp != null ? timestamp.toLocalDateTime() : null);
                    cardInfo.setOpenMoney(rs.getBigDecimal("openMoney"));
                    cardInfo.setBalance(rs.getBigDecimal("balance"));
                    cardInfo.setPass(rs.getString("pass"));
                    cardInfo.setIsReportLoss(rs.getString("isReportLoss"));
                    cardInfo.setCustomerId(rs.getInt("customerId"));
                    cardInfoList.add(cardInfo);
                }
            }
        }catch(SQLException e) {
            e.printStackTrace();
            return null;
        }
        return cardInfoList;
    }


    // 更新银行卡信息
    public boolean updateCardInfo(CardInfo cardInfo) {
        String sql = "update cardinfo set curid=?, savingID=?, opendate=?, openMoney=?, balance=?, pass=?, isReportloss=?, customerid=? where cardId=?";

        try(Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardInfo.getCurId());
            ps.setInt(2, cardInfo.getSavingId());
            ps.setTimestamp(3,Timestamp.valueOf(cardInfo.getOpenDate()));
            ps.setBigDecimal(4,cardInfo.getOpenMoney());
            ps.setBigDecimal(5,cardInfo.getBalance());
            ps.setString(6,cardInfo.getPass());
            ps.setString(7,cardInfo.getIsReportLoss());
            ps.setInt(8,cardInfo.getCustomerId());
            ps.setString(9, cardInfo.getCardId());

            System.out.println(ps.toString());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    // 删除银行卡
    public boolean deleteCardInfo(String cardID){
        String sql = "delete from cardinfo where cardId = ?";
        try (Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }


    // 取消挂失
    public boolean cancelLost(String cardID){
        String sql = "update cardinfo set IsReportLoss = '否' where cardId = ?";

        try(Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }

    // 开户
    public String insertNewCardInfo(CardInfo cardInfo) {
        String cardID= generateCardId();
        String sql = "INSERT INTO cardInfo (cardID, savingID, openMoney, balance, customerID) VALUES (?, ?, ?, ?, ?);";

        try(Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, cardID);
            ps.setInt(2, cardInfo.getSavingId());
            ps.setBigDecimal(3, cardInfo.getOpenMoney());
            ps.setBigDecimal(4, cardInfo.getOpenMoney());
            ps.setInt(5, cardInfo.getCustomerId());
            ps.executeUpdate();

            System.out.println(ps.toString());
        }catch (Exception e){
            e.printStackTrace();
            return "wrong";
        }
        return cardID;
    }

    // 获取存款ID列表
    public List<String> getAllDepositType(){
        List<String> depositTypeList = new LinkedList<>();
        String sql = "select savingName from deposit";
        try(Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                depositTypeList.add(rs.getString("savingName"));
            }
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return depositTypeList;
    }

    // 获取存款信息列表
    public List<Deposit> getAllDeposit(){
        List<Deposit> depositTypeList = new LinkedList<>();
        String sql = "select * from deposit";
        try(Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Deposit deposit = new Deposit();
                deposit.setSavingId(rs.getInt("savingId"));
                deposit.setSavingName(rs.getString("savingName"));
                deposit.setDescription(rs.getString("descrip"));

                depositTypeList.add(deposit);
            }
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return depositTypeList;
    }

    // 更新存款信息
    public boolean updateDeposit(Deposit deposit){
        String sql = "update deposit set savingName=?, descrip=? where savingID=?";
        try(Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, deposit.getSavingName());
            ps.setString(2, deposit.getDescription());
            ps.setInt(3,deposit.getSavingId());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }


    // 增加新存款业务
    public boolean addNewDeposit(Deposit deposit) {
        String sql = "INSERT INTO deposit (savingName, descrip) values (?, ?);";

        try(Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, deposit.getSavingName());
            ps.setString(2, deposit.getDescription());
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    // 删除存款业务
    public  boolean deleteDeposit(int savingID){
        String sql = "delete from deposit where savingID = ?";

        try (Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, savingID);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    // 获取交易信息
    public List<TradeInfo> getTradeInfo(){
        List<TradeInfo> tradeInfos = new LinkedList<>();
        String sql = "select * from tradeInfo";
        try(Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                TradeInfo tradeInfo = new TradeInfo();
                tradeInfo.setTradeId(rs.getString("tradeId"));
                tradeInfo.setTradeType(rs.getString("tradeType"));
                LocalDateTime time = rs.getTimestamp("tradeDate").toLocalDateTime();
                tradeInfo.setTradeDate(time);
                tradeInfo.setTradeMoney(rs.getBigDecimal("tradeMoney"));
                tradeInfo.setRemark(rs.getString("remark"));

                tradeInfos.add(tradeInfo);
            }

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return tradeInfos;
    }

   // 获取所用交易信息
    public List<TradeInfo> getAllTradeInfo(){
        List<TradeInfo> tradeInfos = new LinkedList<>();
        String sql = "select * from tradeInfo";
        try(Connection conn = getConnection();
        PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                TradeInfo tradeInfo = new TradeInfo();
                tradeInfo.setTradeId(rs.getString("tradeId"));
                tradeInfo.setTradeType(rs.getString("tradeType"));
                tradeInfo.setTradeDate(rs.getTimestamp("tradeDate").toLocalDateTime());
                tradeInfo.setTradeMoney(rs.getBigDecimal("tradeMoney"));
                tradeInfo.setCardId(rs.getString("cardid"));
                tradeInfo.setRemark(rs.getString("remark"));
                tradeInfos.add(tradeInfo);
            }

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        return tradeInfos;
    }

    // 通过条件查询交易信息 (SB代码，最好修改美化一下（；´д｀）ゞ)
    public List<TradeInfo> getTradeInfoByCondition(TransactionManagementPanel.TradeInfoFind tradeInfoFind){

        List<TradeInfo> tradeInfos = new LinkedList<>();

        String sql="";

        if(tradeInfoFind.getTradeType()!="全部"){
            if(tradeInfoFind.getCustomerId() == null){
                sql = "select * from tradeInfo WHERE tradeType = ?  AND tradeDate BETWEEN ? AND ? ORDER BY tradeDate DESC" ;
                try(Connection conn = getConnection();
                    PreparedStatement ps = conn.prepareStatement(sql)) {
                    ps.setString(1, tradeInfoFind.getTradeType());
                    ps.setString(2, (tradeInfoFind.getTradeDate1()).toString());
                    ps.setString(3, (tradeInfoFind.getTradeDate2()).toString());

                    System.out.println(ps.toString());
                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        TradeInfo tradeInfo = new TradeInfo();
                        tradeInfo.setTradeId(rs.getString("tradeId"));
                        tradeInfo.setTradeType(rs.getString("tradeType"));
                        tradeInfo.setTradeDate(rs.getTimestamp("tradeDate").toLocalDateTime());
                        tradeInfo.setTradeMoney(rs.getBigDecimal("tradeMoney"));
                        tradeInfo.setCardId(rs.getString("cardid"));
                        tradeInfo.setRemark(rs.getString("remark"));
                        tradeInfos.add(tradeInfo);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }else {
                sql = "select * from tradeInfo WHERE cardID  = ? AND tradeType = ?  AND tradeDate BETWEEN ? AND ? ORDER BY tradeDate DESC" ;
                try(Connection conn = getConnection();
                    PreparedStatement ps = conn.prepareStatement(sql)) {
                    ps.setString(1, tradeInfoFind.getCustomerId().toString());
                    ps.setString(2, tradeInfoFind.getTradeType());
                    ps.setString(3, (tradeInfoFind.getTradeDate1()).toString());
                    ps.setString(4, (tradeInfoFind.getTradeDate2()).toString());

                    System.out.println(ps.toString());

                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        TradeInfo tradeInfo = new TradeInfo();
                        tradeInfo.setTradeId(rs.getString("tradeId"));
                        tradeInfo.setTradeType(rs.getString("tradeType"));
                        tradeInfo.setTradeDate(rs.getTimestamp("tradeDate").toLocalDateTime());
                        tradeInfo.setTradeMoney(rs.getBigDecimal("tradeMoney"));
                        tradeInfo.setCardId(rs.getString("cardid"));
                        tradeInfo.setRemark(rs.getString("remark"));
                        tradeInfos.add(tradeInfo);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }
        }else {
            if(tradeInfoFind.getCustomerId() == null){
                sql = "select * from tradeInfo WHERE   AND tradeDate BETWEEN ? AND ? ORDER BY tradeDate DESC" ;
                try(Connection conn = getConnection();
                    PreparedStatement ps = conn.prepareStatement(sql)) {
                    ps.setString(1, (tradeInfoFind.getTradeDate1()).toString());
                    ps.setString(2, (tradeInfoFind.getTradeDate2()).toString());

                    System.out.println(ps.toString());
                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        TradeInfo tradeInfo = new TradeInfo();
                        tradeInfo.setTradeId(rs.getString("tradeId"));
                        tradeInfo.setTradeType(rs.getString("tradeType"));
                        tradeInfo.setTradeDate(rs.getTimestamp("tradeDate").toLocalDateTime());
                        tradeInfo.setTradeMoney(rs.getBigDecimal("tradeMoney"));
                        tradeInfo.setCardId(rs.getString("cardid"));
                        tradeInfo.setRemark(rs.getString("remark"));
                        tradeInfos.add(tradeInfo);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }else {
                sql = "select * from tradeInfo WHERE cardID  = ? AND tradeDate BETWEEN ? AND ? ORDER BY tradeDate DESC" ;
                try(Connection conn = getConnection();
                    PreparedStatement ps = conn.prepareStatement(sql)) {
                    ps.setString(1, tradeInfoFind.getCustomerId().toString());
                    ps.setString(2, (tradeInfoFind.getTradeDate1()).toString());
                    ps.setString(3, (tradeInfoFind.getTradeDate2()).toString());

                    System.out.println(ps.toString());

                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        TradeInfo tradeInfo = new TradeInfo();
                        tradeInfo.setTradeId(rs.getString("tradeId"));
                        tradeInfo.setTradeType(rs.getString("tradeType"));
                        tradeInfo.setTradeDate(rs.getTimestamp("tradeDate").toLocalDateTime());
                        tradeInfo.setTradeMoney(rs.getBigDecimal("tradeMoney"));
                        tradeInfo.setCardId(rs.getString("cardid"));
                        tradeInfo.setRemark(rs.getString("remark"));
                        tradeInfos.add(tradeInfo);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    return null;
                }
            }
        }


        return tradeInfos;

    }


    // 随机生成cardid
    public static String generateCardId() {
        Random random = new Random();
        // 生成8位随机数字
        StringBuilder suffix = new StringBuilder();
        for (int i = 0; i < 8; i++) {
            suffix.append(random.nextInt(10));
        }
        return "10103576" + suffix;
    }
}
